package telebase;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class DB {

    private Connection conn = null;
    private String url = null;
    private Statement st = null;
    private ResultSet rs = null;
    private String sql = null;
    private String username = null;
    private String userpass = null;
    private Boolean lock = false;

    public DB() {
        this.username = Vars.prop.getProperty("Username");
        this.userpass = Vars.prop.getProperty("Userpass");
        this.url = Vars.prop.getProperty("DB_URL");

        if (this.username == null || this.userpass == null || this.url == null) {
            System.out.println("DB.DB(): username,userpass or url == NULL!");
        }
    }

    private void createConnection() {
        try {
            Properties info = new Properties();
            info.setProperty("user", this.username);
            info.setProperty("password", this.userpass);
            info.setProperty("useUnicode", "true");
            info.setProperty("characterEncoding", "UTF-8");
            this.conn = DriverManager.getConnection(this.url, info);
        } catch (SQLException ex) {
            System.out.println("DB.createConnection(): " + ex.toString());
        }
    }

    private void closeConnection() {
        try {
            this.st.close();
            this.conn.close();
        } catch (SQLException ex) {
            System.out.println("DB.closeConnection(): " + ex.toString());
        }
        this.rs = null;
        this.st = null;
        this.conn = null;
        this.sql = null;
    }

    private void createStatement() {
        try {
            //this.st = this.conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            this.st = this.conn.createStatement();
        } catch (SQLException ex) {
            System.out.println("DB.createStatement(): " + ex.toString());
        }
    }

    private void createResultSet() {
        try {
            this.rs = this.st.executeQuery(this.sql);
        } catch (SQLException ex) {
            System.out.println("DB.createResultSet(): " + ex.toString());
        }
    }

    public ResultSet getResultSet(String sql) {
        if (!this.lock) {
            this.sql = sql;
            this.createConnection();
            this.createStatement();
            this.createResultSet();
            this.lock = true;
            return this.rs;
        } else {
            System.out.println("DB.getResultSet(): connection locked");
            return null;
        }
    }

    public void closeResultSet() {
        if (this.lock) {
            this.closeConnection();
            this.lock = false;
        } else {
            System.out.println("DB.closeResultSet(): nothing to close");
        }
    }

    public void executeNonQuery(String sql) {
        if (!this.lock) {
            this.createConnection();
            this.createStatement();
            try {
                this.st.execute(sql);
            } catch (SQLException ex) {
                System.out.println("DB.executeNonQuery(): " + ex.toString());
            }
            this.closeConnection();
        } else {
            System.out.println("DB.executeNonQuery(): connection locked.");
        }
    }

    public int executeNonQueryAI(String sql) {
        int AI = -1;
        if (!this.lock) {
            this.createConnection();
            this.createStatement();
            try {
                this.st.execute(sql, Statement.RETURN_GENERATED_KEYS);
                this.rs = this.st.getGeneratedKeys();
                this.rs.next();
                AI = this.rs.getInt(1);
            } catch (SQLException ex) {
                System.out.println("DB.executeNonQuery(): " + ex.toString());
            }
            this.closeConnection();
        } else {
            System.out.println("DB.executeNonQuery(): connection locked.");
        }
        return AI;
    }

    public Object getScalar(String sql) {
        Object o = null;
        if (!this.lock) {
            this.sql = sql;
            this.createConnection();
            this.createStatement();
            this.createResultSet();
            try {
                this.rs.next();
                o = rs.getObject(1);
            } catch (SQLException ex) {
                System.out.println("DB.executeScalar(): " + ex.toString());
            } catch (Exception ex) {
                System.out.println("DB.executeScalar(): " + ex.toString());
            }
            this.closeConnection();
        } else {
            System.out.println("DB.executeScalar(): connection locked.");
        }
        return o;
    }

    public String getPrepS(String string) {
        String s;
        s = string.replaceAll("\"", "\"\"");
        return s;
    }
}
